﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace CarInfor.DAL
{
    /// <summary>
    /// 保险单
    /// </summary>
    public class InsuranceForm
    {

        #region 属性定义
        /// <summary>
        /// ID主键标识
        /// </summary>
        private string id = "";
        public string ID
        {
            get { return id; }
            set { id = value; }
        }

        /// <summary>
        /// 保险单号
        /// </summary>
        private string insuranceNumber = "";
        public string InsuranceNumber
        {
            get { return insuranceNumber; }
            set { insuranceNumber = value; }
        }

        /// <summary>
        /// 商业保险单号
        /// </summary>
        private string bussInsuranceNumber = "";
        public string BussInsuranceNumber
        {
            get { return bussInsuranceNumber; }
            set { bussInsuranceNumber = value; }
        }

        /// <summary>
        /// 被保险人
        /// </summary>
        private string ownerName = "";
        public string OwnerName
        {
            get { return ownerName; }
            set { ownerName = value; }
        }

        /// <summary>
        /// 参保日期
        /// </summary>
        private string startDate = "";
        public string StartDate
        {
            get { return startDate; }
            set { startDate = value; }
        }

        /// <summary>
        /// 到期日期
        /// </summary>
        private string endDate = "";
        public string EndDate
        {
            get { return endDate; }
            set { endDate = value; }
        }

        /// <summary>
        /// 商业险参保日期
        /// </summary>
        private string bussStartDate = "";
        public string BussStartDate
        {
            get { return bussStartDate; }
            set { bussStartDate = value; }
        }

        /// <summary>
        /// 商业保险到期日期
        /// </summary>
        private string bussEndDate = "";
        public string BussEndDate
        {
            get { return bussEndDate; }
            set { bussEndDate = value; }
        }

        /// <summary>
        /// 电话
        /// </summary>
        private string telephone = "";
        public string Telephone
        {
            get { return telephone; }
            set { telephone = value; }
        }

        /// <summary>
        /// 车辆号牌
        /// </summary>
        private string carNumber = "";
        public string CarNumber
        {
            get { return carNumber; }
            set { carNumber = value; }
        }

        /// <summary>
        /// 车架号
        /// </summary>
        private string frameNumber = "";
        public string FrameNumber
        {
            get { return frameNumber; }
            set { frameNumber = value; }
        }

        /// <summary>
        /// 报户时间
        /// </summary>
        private string baoHuDate = "";
        public string BaoHuDate
        {
            get { return baoHuDate; }
            set { baoHuDate = value; }
        }

        /// <summary>
        /// 车辆类型
        /// </summary>
        private string carType = "";
        public string CarType
        {
            get { return carType; }
            set { carType = value; }
        }

        /// <summary>
        /// 使用性质
        /// </summary>
        private string useProperty = "";
        public string UseProperty
        {
            get { return useProperty; }
            set { useProperty = value; }
        }

        /// <summary>
        /// 核定
        /// </summary>
        private decimal weight;
        public decimal Weight
        {
            get { return weight; }
            set { weight = value; }
        }

        /// <summary>
        /// 单位
        /// </summary>
        private string unit = "";
        public string Unit
        {
            get { return unit; }
            set { unit = value; }
        }

        /// <summary>
        /// 保险合计金额
        /// </summary>
        private decimal total;
        public decimal Total
        {
            get { return total; }
            set { total = value; }
        }

        /// <summary>
        /// 备注
        /// </summary>
        private string memo;
        public string Memo
        {
            get { return memo; }
            set { memo = value; }
        }


        #endregion

        #region 方法
        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataTable Query()
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(""));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataSet QueryForReport()
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(""));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds;
        }

        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataSet QueryForReport(string condition)
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(condition));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds;
        }
        /// <summary>
        /// 有条件查询
        /// </summary>
        public DataTable Query(string condition)
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(condition));
            }
            catch (Exception e)
            {
                throw e;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据主键查询
        /// </summary>
        public void QueryWithKey(string keyValue)
        {
            DataSet ds = null;
            try
            {
                string cond = "ID='" + keyValue + "'";
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(cond));
                if (ds.Tables[0].Rows.Count > 0)
                {
                    id = ds.Tables[0].Rows[0]["编号"].ToString();
                    insuranceNumber = ds.Tables[0].Rows[0]["交强险保单号"].ToString();
                    bussInsuranceNumber = ds.Tables[0].Rows[0]["商业险保单号"].ToString();
                    ownerName = ds.Tables[0].Rows[0]["被保险人"].ToString();
                    startDate = ds.Tables[0].Rows[0]["交强险参保日期"].ToString();
                    endDate = ds.Tables[0].Rows[0]["交强险到期日期"].ToString();
                    bussStartDate = ds.Tables[0].Rows[0]["商业险参保日期"].ToString();
                    bussEndDate = ds.Tables[0].Rows[0]["商业险到期日期"].ToString();
                    telephone = ds.Tables[0].Rows[0]["联系电话"].ToString();
                    CarNumber = ds.Tables[0].Rows[0]["车辆号牌"].ToString();
                    carType = ds.Tables[0].Rows[0]["车辆类型"].ToString();
                    frameNumber = ds.Tables[0].Rows[0]["车架号"].ToString();
                    useProperty = ds.Tables[0].Rows[0]["使用性质"].ToString();
                    weight = decimal.Parse(ds.Tables[0].Rows[0]["核定"].ToString());
                    unit = ds.Tables[0].Rows[0]["单位"].ToString();
                    total = decimal.Parse(ds.Tables[0].Rows[0]["金额"].ToString());
                    memo = ds.Tables[0].Rows[0]["备注"].ToString();

                }
                else
                {
                    id = "";
                    insuranceNumber = "";
                    bussInsuranceNumber = "";
                    ownerName = "";
                    startDate = "";
                    endDate = "";
                    bussStartDate = "";
                    bussEndDate = "";
                    telephone = "";
                    carNumber = "";
                    carType = "";
                    frameNumber = "";
                    useProperty = "";
                    unit = "";
                    memo = "";
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        /// <summary>
        /// 获取查询Sql
        /// </summary>
        public string GetSqlString(string condition)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("SELECT ");
                sql.Append(" ID as 编号,CarNumber as 车辆号牌,OwnerName as 被保险人,Telephone as 联系电话,InsuranceNumber as 交强险保单号, StartDate as 交强险参保日期,EndDate as 交强险到期日期,BussInsuranceNumber as 商业险保单号,BussStartDate as 商业险参保日期,BussEndDate as 商业险到期日期, CarType as 车辆类型,frameNumber as 车架号,Total as 金额,UseProperty as 使用性质,BaoHuDate as 报户日期,Weight as 核定,Unit as 单位,Memo as 备注 ");
                sql.Append(" FROM InsuranceForm");
                if (!string.IsNullOrEmpty(condition))
                {
                    sql.Append(" WHERE " + condition + " order by EndDate  ");
                }
                else
                {
                    sql.Append(" order by EndDate ");
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            return sql.ToString();

        }


        /// <summary>
        /// 通过主键更新
        /// </summary>
        public int UpdateWithKey(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("UPDATE InsuranceForm SET ");
                sql.Append("[OwnerName]='" + format(ownerName) + "'");

                //交强险信息
                if (!string.IsNullOrEmpty(insuranceNumber))
                {
                    sql.Append(",[InsuranceNumber]='" + format(insuranceNumber) + "'");
                    sql.Append(",[StartDate]='" + format(startDate) + "'");
                    sql.Append(",[EndDate]='" + format(endDate) + "'");

                }
                else
                {
                    sql.Append(",[InsuranceNumber]=NULL ");
                    sql.Append(",[StartDate]=NULL ");
                    sql.Append(",[EndDate]=NULL ");
                }

                //商业险信息
                if (!string.IsNullOrEmpty(bussInsuranceNumber))
                {
                    sql.Append(",[BussInsuranceNumber]='" + format(bussInsuranceNumber) + "'");
                    sql.Append(",[BussStartDate]='" + format(bussStartDate) + "'");
                    sql.Append(",[BussEndDate]='" + format(bussEndDate) + "'");

                }
                else
                {
                    sql.Append(",[BussInsuranceNumber]=NULL ");
                    sql.Append(",[BussStartDate]=NULL ");
                    sql.Append(",[BussEndDate]=NULL ");
                }
                sql.Append(",[Telephone]='" + format(telephone) + "'");
                sql.Append(",[CarNumber]='" + format(carNumber) + "'");
                sql.Append(",[CarType]='" + format(carType) + "'");
                sql.Append(",[FrameNumber]='" + format(frameNumber) + "'");
                sql.Append(",[BaoHuDate]='" + format(baoHuDate) + "'");
                sql.Append(",[UseProperty]='" + format(useProperty) + "'");
                sql.Append(",[Weight]=" + weight);
                sql.Append(",[Unit]='" + format(unit) + "'");
                sql.Append(",[Total]=" + total);
                sql.Append(",[Memo]='" + format(memo) + "'");
                sql.Append(" WHERE ID='" + keyValue + "'");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }


        /// <summary>
        ///添加一条记录
        /// </summary>
        public int Insert()
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("INSERT into InsuranceForm");
                if (!string.IsNullOrEmpty(insuranceNumber) && !string.IsNullOrEmpty(bussInsuranceNumber))
                {
                    sql.Append("([ID],[OwnerName],[InsuranceNumber],[StartDate],[EndDate],[BussInsuranceNumber],[BussStartDate],[BussEndDate],[Telephone],[CarNumber],[CarType],[FrameNumber],[BaoHuDate],[UseProperty],[Weight],[Unit],[Total],[Memo]) ");
                }
                else if (!string.IsNullOrEmpty(insuranceNumber))
                {
                    sql.Append("([ID],[OwnerName],[InsuranceNumber],[StartDate],[EndDate],[Telephone],[CarNumber],[CarType],[FrameNumber],[BaoHuDate],[UseProperty],[Weight],[Unit],[Total],[Memo]) ");
                }
                else if (!string.IsNullOrEmpty(bussInsuranceNumber))
                {
                    sql.Append("([ID],[OwnerName],[BussInsuranceNumber],[BussStartDate],[BussEndDate],[Telephone],[CarNumber],[CarType],[FrameNumber],[BaoHuDate],[UseProperty],[Weight],[Unit],[Total],[Memo]) ");
                }
                sql.Append(" VALUES(");
                sql.Append("'" + format(id) + "'");
                sql.Append(",'" + format(ownerName) + "'");

                if (!string.IsNullOrEmpty(insuranceNumber) && !string.IsNullOrEmpty(bussInsuranceNumber))
                {
                    sql.Append(",'" + format(insuranceNumber) + "'");
                    sql.Append(",'" + format(startDate) + "'");
                    sql.Append(",'" + format(endDate) + "'");
                    sql.Append(",'" + format(bussInsuranceNumber) + "'");
                    sql.Append(",'" + format(bussStartDate) + "'");
                    sql.Append(",'" + format(bussEndDate) + "'");
                }
                else if (!string.IsNullOrEmpty(insuranceNumber))
                {
                    sql.Append(",'" + format(insuranceNumber) + "'");
                    sql.Append(",'" + format(startDate) + "'");
                    sql.Append(",'" + format(endDate) + "'");
                }
                else if (!string.IsNullOrEmpty(bussInsuranceNumber))
                {
                    sql.Append(",'" + format(bussInsuranceNumber) + "'");
                    sql.Append(",'" + format(bussStartDate) + "'");
                    sql.Append(",'" + format(bussEndDate) + "'");
                }
                sql.Append(",'" + format(telephone) + "'");
                sql.Append(",'" + format(carNumber) + "'");
                sql.Append(",'" + format(carType) + "'");
                sql.Append(",'" + format(frameNumber) + "'");
                sql.Append(",'" + format(baoHuDate) + "'");
                sql.Append(",'" + format(useProperty) + "'");
                sql.Append("," + weight + "");
                sql.Append(",'" + format(unit) + "'");
                sql.Append("," + total + "");
                sql.Append(",'" + format(memo) + "'");
                sql.Append(")");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 要求在调用IsDelete（）后使用
        /// 返回为true时，表示删除成功
        /// 返回为false时，表示删除失败
        /// </summary>
        public bool Delete(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append(" DELETE FROM InsuranceForm ");
                sql.Append(" WHERE ID='" + keyValue + "'");
                db.ExecuteSql(sql.ToString());
            }
            catch (Exception e)
            {
                throw (e);
            }
            return true;
        }

        /// <summary>
        /// 单引号的处理
        /// </summary>
        public string format(string myString)
        {
            return myString.Replace("'", "''");
        }

        #endregion

    }
}
